﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CommClass;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DMS
{
    public partial class FrmInStock : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile;
        public FrmInStock()
        {
            InitializeComponent();
        }

        private void FrmInStock_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';Data Source=" + tbFileName.Text;
            OleDbConnection myOleDbConnection = new OleDbConnection(constr);
            OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter("select * from [" + cbbSheets.Text + "] order by 入库单号 ", myOleDbConnection);
            DataTable myDataTable = new DataTable();
            myOleDbDataAdapter.Fill(myDataTable);
            //显示excel数据
           // dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "]").Tables[0];
            dvginstock.DataSource = myDataTable;
            this.button3.Enabled = true;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Close();
        }
        //检查EXCEL数据是否符合导入要求
        public string checkExcelData(DataTable dt, SqlConnection connection, SqlCommand cmd)
        {
            string listinfo = "";
            int StorageID;
            int SupplierID;
            int ProdID;

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                StorageID = App.GetStorageID(dt.Rows[j][14].ToString(), connection, cmd);
                SupplierID = App.GetSupplierID(dt.Rows[j][10].ToString(), connection, cmd);
                ProdID = App.GetProdIDByCode(dt.Rows[j][7].ToString(), connection, cmd);
               
                //库位，货权企业和产品都能匹配到
                if (StorageID > 0 && SupplierID > 0 && ProdID > 0)
                {
                       //判断单据是否已存在
                      //  if (App.CheckInStockNumber(dt.Rows[0][0].ToString()))
                      //  {

                     //       lstInfo .Items .Add (dt.Rows[0][0] + "该提单系统中已存在被忽略。");
                      //      listinfo=dt.Rows[0][0].ToString ();
                      //      break;

                      //  }

                 }
                else
                {
                    lstInfo .Items .Add ("单号:" + dt.Rows[j][0] + ",库位:" + dt.Rows[j][2] + ",货权企业:" + dt.Rows[j][3] + ",品项：" + dt.Rows[j][4] + "与系统不符，请核实。");
                    listinfo = dt.Rows[0][0].ToString();
                    continue;

                }

            }
            return listinfo;

        }


        private void button3_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            DataTable dtt = (DataTable)dvginstock.DataSource;
            StockManage sm = new StockManage();

            string instocknumber ="";
            if (dtt.Rows.Count > 0)
            {
                 using (SqlConnection conn = new SqlConnection(App.GetSqlConnection()))
                {
                    conn.Open();
                    using (SqlTransaction tran = conn.BeginTransaction()) //开始数据库事务。即创建一个事务对象tran  
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.Connection = conn;
                            cmd.Transaction = tran; //获取或设置将要其执行的事务  
                           try
                            {
                                for (int i = 0; i < dtt.Rows.Count; )
                                {
                                    DataRow[] dr;
                                    DataTable dt;
                                    dr = dtt.Select("入库单号='" + dtt.Rows[i][0].ToString() + "'");
                                    dt = dr.CopyToDataTable();
                                    instocknumber = checkExcelData(dt,conn, cmd);
                                    if (instocknumber == "")
                                    {
                                       // sm.StockDate = DateTime.Parse(dt.Rows[0][1].ToString());

                                        InStock ins = new InStock();
                                        //ins.InStockNumber;
                                        ins.CaigouNumber = dt.Rows[0][1].ToString().Trim();
                                        ins.InStockDate = DateTime.Parse(dt.Rows[0][2].ToString().Trim());
                                        ins.JieDanDate = DateTime.Parse(dt.Rows[0][3].ToString().Trim());
                                        ins.UserID = App.GetUserID(dt.Rows[0][4].ToString(), conn, cmd);
                                        ins.InStockTypeID = App.GetInStockTypeID(dt.Rows[0][5].ToString(), conn, cmd);
                                        ins.InStockStatusID = App.GetInStockStatusID(dt.Rows[0][6].ToString(), conn, cmd);
                                        ins.SupplierID = App.GetSupplierID(dt.Rows[0][10].ToString(), conn, cmd);
                                        ins.SupplySupplierID = App.getSupplySupplierID(dt.Rows[0][11].ToString(), conn, cmd);
                                        ins.CarrierID = App.getCarrierID(dt.Rows[0][12].ToString(), conn, cmd);
                                        ins.ShouHuoID = App.GetUserID(dt.Rows[0][16].ToString(), conn, cmd);
                                        ins.StoreID =  App.getStoreID(dt.Rows[0][13].ToString(), conn, cmd);
                                        ins.TuiHuoKeHu = dt.Rows[0][20].ToString().Trim();
                                        ins.TuiHuoRemark = dt.Rows[0][21].ToString().Trim();
                                        ins.Remark = dt.Rows[0][22].ToString().Trim();
                                        
                                        ins.InStockID  = ins.saveInStock(ins, conn, cmd);
                                         ins.updateInStockZhipai(ins, conn, cmd);


                                            for (int j = 0; j < dt.Rows.Count; j++)
                                            {
                                                 InStockEntry inse = new InStockEntry();
                                    //inse.InStockEntryID;
                                    inse.InStockID = ins.InStockID;
                                    inse.ProdID = App.GetProdIDByCode(dt.Rows[j][7].ToString(), conn, cmd);
                                    inse.ProdTypeID = App.GetProdtypeByproduct(Convert.ToString(inse.ProdID), conn, cmd);
                                    inse.StorageID = App.GetStorageID(dt.Rows[j][14].ToString(), conn, cmd);
                                    inse.Batch = dt.Rows[j][15].ToString().Trim();
                                    inse.ProduceDate = DateTime.Parse( dt.Rows[j][17].ToString().Trim());
                                    inse.StockPinZhiID =  App.GetStockPinZhiIDByname(dt.Rows[j][18].ToString(), conn, cmd);
                                    inse.StockQtyYuBao = Double.Parse(dt.Rows[j][19].ToString().Trim());
                                     inse.StockQty = Double.Parse(dt.Rows[j][19].ToString().Trim());;
                                    inse.Remark = "";
                                     inse.InStockEntryID = inse.saveInStockEntry(inse, conn, cmd);
                                     inse.updateInStockEntryHuowei(inse, conn, cmd);
                                                //增加库存
                                    Stock st = new Stock();
                                    st.ProdID = inse.ProdID;
                                    st.ProdTypeID = inse.ProdTypeID;
                                    st.SupplierID = ins.SupplierID;
                                    st.StorageID = inse.StorageID;
                                    st.Batch = inse.Batch;
                                    st.ProduceDate = inse.ProduceDate;
                                     st.InputDate =  DateTime.Now;
                                     st.StockPinZhiID = inse.StockPinZhiID;
                                     st.StockQty = Convert.ToDecimal(inse.StockQty);
                                     st.StockUseableQty = Convert.ToDecimal(inse.StockQty);
                                     st.Remark = "";
                                     st.InStockNumber = Convert.ToString(inse.InStockEntryID);
                                     st.saveStock(st, conn, cmd);
                                            }

                                    }
                                    i = i + dt.Rows.Count;
                                }

                                tran.Commit();
                                conn.Close();
                                lstInfo.Items.Add("数据导入完成！");
                            }
                           catch
                            {

                                tran.Rollback();//如果执行不成功，发送异常，则执行rollback方法，回滚到事务操作开始之前。
                                conn.Close();
                                lstInfo.Items.Add("数据导入失败！");
                            }

                        }
                    }

                }

            }
                     
        }

    }
}
